package com.demo2do.fms.admin.entity.report.supplier;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.WeakHashMap;

import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 *
 * @author Ark
 *
 */
public class VehicleSourceReportSearch implements QueryBuilder {

    /** 所属供方 */
    private Long supplierId;
    
    /** 车辆同步状态 */
    private String status;
    
    /** 是否同步 */
    private Boolean synchronize;
    
    /** 是否锁定 */
    private Boolean lock;
    
    private QueryOrder queryOrder;
    
    private QueryFilter queryFilter;
    
    /**
     * The default constructor
     */
    public VehicleSourceReportSearch() {
        queryOrder = new QueryOrder("v.tractor_brand_code", "ASC");
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter().addEquals("s", "id", supplierId)
                                           .addEquals("v", "status", status)
                                           .addEquals("v", "is_synchronize", synchronize)
                                           .addEquals("v", "is_lock", lock);
            
        }
        
        return queryFilter;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(1024);
        
        buffer.append("SELECT v.id AS id, ")
                     .append("v.vehicle_code AS vehicleCode, ")
                     .append("s.name AS supplierName, ")
                     .append("s.short_name AS supplierSimpleName, ")
                     .append("CASE WHEN v.status = 'UNSYNC' THEN '整车编辑' WHEN v.status = 'SYNCING' THEN '待引入' WHEN v.status = 'SYNCED' THEN '已引入' WHEN v.status = 'REJECTED' THEN '已驳回' END AS status, ")
                     .append("v.is_synchronize AS synchronize, ")
                     .append("CASE WHEN v.vehicle_status = 'NORMAL' THEN '正常' WHEN v.vehicle_status = 'OUT' THEN '转出' END AS vehicleStatus, ")
                     .append("v.is_lock AS locking, ")
                     .append("CASE WHEN v.lock_type = 'SHIPMENT_EXCEPTION' THEN '调度异常锁定' WHEN v.lock_type = 'BUSINESS_EXCEPTION' THEN '业务运作锁定' WHEN v.lock_type = 'TRANS_MANAGEMENT' THEN '运力管理锁定' WHEN v.lock_type = 'FREEZE' THEN '冻结' WHEN v.lock_type = 'SHIPMENT_OVERFLOW' THEN '日调度超限' WHEN v.lock_type = 'INVALID_VEHICLE' THEN '牵引车、挂车未审核通过或未绑定' END AS lockType, ")
                     .append("v.lock_reason AS lockReason, ")
                     .append("v.reject_reason AS rejectReason, ")
                     .append("v.declare_time AS declareTime, ")
                     .append("NVL2(ta.trainload, ta.trainload, 0) + NVL2(ti.trainload, ti.trainload, 0) AS vehicleTrainload, ")
                     .append("ta.brand_cn_code AS tractorBrandCnCode, ")
                     .append("ta.brand_en_code AS tractorBrandEnCode, ")
                     .append("ta.floor_lord AS tractorFloorLord, ")
                     .append("CASE WHEN ta.car_ownership = 'OWN' THEN '自有' WHEN ta.car_ownership = 'HOLDING' THEN '控股' WHEN ta.car_ownership = 'NOTCONVERTED' THEN '未转籍' WHEN ta.car_ownership = 'LEASE' THEN '租赁' WHEN ta.car_ownership = 'PERSONALANCHORS' THEN '个人挂靠' WHEN ta.car_ownership = 'COMPANYANCHORS' THEN '公司挂靠' END AS carOwnership, ")
                     .append("CASE WHEN ta.trans_mean = 'GOODSVEHICLES' THEN '商品车运输车辆' WHEN ta.trans_mean = 'FLAT' THEN '平板/栏板车' WHEN ta.trans_mean = 'WAREHOUSE' THEN '仓储内驳车' WHEN ta.trans_mean = 'RESCUE' THEN '救援车' END AS transMean, ")
                     .append("ta.gps AS gps, ")
                     .append("ta.sim_card AS simCard, ")
                     .append("CASE WHEN ta.status = 'EDITING' THEN '编辑中' WHEN ta.status = 'AUDITING' THEN '待审核' WHEN ta.status = 'REJECTED' THEN '驳回' WHEN ta.status = 'QUALIFIED' THEN '合格' WHEN ta.status = 'MODIFIED' THEN '变更申报' END AS tractorStatus, ")
                     .append("ta.vin AS tractorVincode, ")
                     .append("ta.brand AS brand, ")
                     .append("ti.brand AS trailerBrand, ")
                     .append("ta.issuing_time AS issuingTime, ")
                     .append("ta.registration_time AS registrationTime, ")
                     .append("ta.trainload as tractorTrainload, ")
                     .append("ta.horsepower AS horsepower, ")
                     .append("CASE WHEN ta.vehicle_type = 'HEAVY_SEMITRAILER' THEN '重型半挂牵引车' WHEN ta.vehicle_type = 'HEAVY_SPECIAL_TRUCK' THEN '重型特殊结构货车' WHEN ta.vehicle_type = 'MEDIUM_NON_CARGO' THEN '中型非载货专项作业车' WHEN ta.vehicle_type = 'HEAVY_TRANSPORT_TRACTOR' THEN '重型车辆运输车' END AS tractorVehicleType, ")
                     .append("ta.axles as tractorAxles, ")
                     .append("ti.floor_lord AS trailerLord, ")
                     .append("ti.brand_cn_code AS trailerBrandCnCode, ")
                     .append("ti.vin AS trailerVincode, ")
                     .append("CASE WHEN ti.status = 'EDITING' THEN '编辑中' WHEN ti.status = 'AUDITING' THEN '待审核' WHEN ti.status = 'REJECTED' THEN '驳回' WHEN ti.status = 'QUALIFIED' THEN '合格' WHEN ti.status = 'MODIFIED' THEN '变更申报' END AS trailerStatus, ")
                     .append("ti.length AS length, ")
                     .append("ti.width AS width, ")
                     .append("ti.height AS height, ")
                     .append("CASE WHEN ti.trailer_type = 'EDITING' THEN '开放式' WHEN ti.trailer_type = 'HALFCLOSED' THEN '半封闭' WHEN ti.trailer_type = 'CLOSED' THEN '全封闭' WHEN ti.trailer_type = 'FLYWING' THEN '飞翼挂' WHEN ti.trailer_type = 'FLAT' THEN 'FLAT' WHEN ti.trailer_type = 'HURDLE' THEN '高栏挂' END AS trailerType, ")
                     .append("ti.trainload AS trailerTrainload, ")
                     .append("ti.annual_inspection_date AS annualInspectionDate, ")
                     .append("CASE WHEN ti.vehicle_type = 'HEAVY_CONTAINER' THEN '重型集装箱半挂车' WHEN ti.vehicle_type = 'HEAVY_TRANSPORT_TRAILER' THEN '重型车辆运输半挂车' WHEN ti.vehicle_type = 'HEAVY_SPECIAL_TRAILER' THEN '重型特殊结构半挂车' WHEN ti.vehicle_type = 'HEAVY_MIDDLE_AXIS' THEN '重型中置轴车辆运输车' WHEN ti.vehicle_type = 'HEAVY_BOX_TRUCK' THEN '重型箱式货车' WHEN ti.vehicle_type = 'COMMON_TRAILER' THEN '普通挂车' WHEN ti.vehicle_type = 'CANISTER_TRAILER' THEN '罐式挂车' WHEN ti.vehicle_type = 'FLAT_TRAILER' THEN '平板挂车' WHEN ti.vehicle_type = 'SELF_STEVEDORING' THEN '自装卸挂车' END AS trailerVehicleType, ")
                     .append("ti.axles AS trailerAxles ")
              .append("FROM fms_vehicle_source v ")
              .append("  LEFT JOIN fms_tractor ta ON v.tractor_id = ta.id AND ta.is_disabled = 0 ")
              .append("  LEFT JOIN fms_trailer ti ON v.trailer_id = ti.id AND ti.is_disabled = 0 ")
              .append("  LEFT JOIN fms_supplier s ON v.supplier_id = s.id AND s.is_disabled = 0 ")
              .append("WHERE v.is_disabled = 0 ")
              .append(getPreparedFilter(false).clauseSentence() + queryOrder.sentence());
        
        return buffer.toString();
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {

            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("id", rs.getObject("id"));
                result.put("vehicleCode", rs.getObject("vehicleCode"));
                result.put("supplierName", rs.getObject("supplierName"));
                result.put("supplierSimpleName", rs.getObject("supplierSimpleName"));
                result.put("status", rs.getObject("status"));
                result.put("synchronize", rs.getBoolean("synchronize"));
                result.put("lock", rs.getBoolean("locking"));
                result.put("lockType", rs.getObject("lockType"));
                result.put("lockReason", rs.getObject("lockReason"));
                result.put("rejectReason", rs.getObject("rejectReason"));
                result.put("declareTime", rs.getObject("declareTime"));
                result.put("vehicleTrainload", rs.getObject("vehicleTrainload"));
                result.put("tractorBrandCnCode", rs.getObject("tractorBrandCnCode"));
                result.put("tractorBrandEnCode", rs.getObject("tractorBrandEnCode"));
                result.put("tractorFloorLord", rs.getObject("tractorFloorLord"));
                result.put("carOwnership", rs.getObject("carOwnership"));
                result.put("transMean", rs.getObject("transMean"));
                result.put("gps", rs.getObject("gps"));
                result.put("simCard", rs.getObject("simCard"));
                result.put("tractorStatus", rs.getObject("tractorStatus"));
                result.put("tractorVincode", rs.getObject("tractorVincode"));
                result.put("brand", rs.getObject("brand"));
                result.put("issuingTime", rs.getObject("issuingTime"));
                result.put("registrationTime", rs.getObject("registrationTime"));
                result.put("tractorTrainload", rs.getObject("tractorTrainload"));
                result.put("horsepower", rs.getObject("horsepower"));
                result.put("tractorVehicleType", rs.getObject("tractorVehicleType"));
                result.put("tractorAxles", rs.getObject("tractorAxles"));
                result.put("trailerLord", rs.getObject("trailerLord"));                
                result.put("trailerBrandCnCode", rs.getObject("trailerBrandCnCode"));
                result.put("trailerVincode", rs.getObject("trailerVincode"));
                result.put("trailerStatus", rs.getObject("trailerStatus"));
                result.put("length", rs.getObject("length"));
                result.put("width", rs.getObject("width"));
                result.put("height", rs.getObject("height"));   
                result.put("trailerType", rs.getObject("trailerType"));
                result.put("trailerTrainload", rs.getObject("trailerTrainload"));
                result.put("annualInspectionDate", rs.getObject("annualInspectionDate"));
                result.put("trailerVehicleType", rs.getObject("trailerVehicleType"));
                result.put("trailerAxles", rs.getObject("trailerAxles"));
                result.put("trailerBrand", rs.getObject("trailerBrand"));

                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return the supplierId
     */
    public Long getSupplierId() {
        return supplierId;
    }

    /**
     * @return the synchronize
     */
    public Boolean getSynchronize() {
        return synchronize;
    }

    /**
     * @return the lock
     */
    public Boolean getLock() {
        return lock;
    }

    /**
     * @return the status
     */
    public String getStatus() {
        return status;
    }

    /**
     * @param supplierId the supplierId to set
     */
    public void setSupplierId(Long supplierId) {
        this.supplierId = supplierId;
    }

    /**
     * @param synchronize the synchronize to set
     */
    public void setSynchronize(Boolean synchronize) {
        this.synchronize = synchronize;
    }

    /**
     * @param lock the lock to set
     */
    public void setLock(Boolean lock) {
        this.lock = lock;
    }

    /**
     * @param status the status to set
     */
    public void setStatus(String status) {
        this.status = status;
    }
    
}